
CREATE     trigger tft_msproduct_bi on dbo.tft_msproduct
instead of insert 
as   
begin
  set nocount on
         
  declare @nbalyear integer   
  declare @nbalmonth integer   
  declare @filid integer
  declare @moneyid integer
   
  declare @bmbala numeric(38,8)   
  declare @bysumdebit numeric(38,8)   
  declare @bysumcredit numeric(38,8)   
  declare @tmsumdebit numeric(38,8)   
  declare @tmsumcredit numeric(38,8)   
  declare @tmsum numeric(38,8)   
   
  declare @fbmbala numeric(38,8)   
  declare @fbysumdebit numeric(38,8)   
  declare @fbysumcredit numeric(38,8)   
  declare @ftmsumdebit numeric(38,8)   
  declare @ftmsumcredit numeric(38,8)   
  declare @ftmsum numeric(38,8)   
   
  declare @bmqtybala numeric(38,8)   
  declare @byqtysumcredit numeric(38,8)   
  declare @byqtysumdebit numeric(38,8)   
  declare @tmqtysumcredit numeric(38,8)   
  declare @tmqtysumdebit numeric(38,8)   

  declare 
    @productid integer,   
    @subjectid integer,
    @nyear integer,   
    @nmonth integer

  declare local_cursor cursor local 
  for select productid,subjectid,nyear,nmonth,filid,moneyid from inserted
  open local_cursor
  fetch next from local_cursor into @productid,@subjectid,@nyear,@nmonth,@filid,@moneyid
  while @@fetch_status=0
  begin  
    set  @nbalyear=NULL;
    set  @nbalmonth=NULL;
    set  @bmbala = 0;
    set  @bysumdebit = 0;
    set  @bysumcredit = 0;
    set  @tmsumdebit = 0;
    set  @tmsumcredit = 0;
    set  @tmsum = 0;
    set  @fbmbala = 0;
    set  @fbysumdebit = 0;
    set  @fbysumcredit = 0;
    set  @ftmsumdebit = 0;
    set  @ftmsumcredit = 0;
    set  @ftmsum = 0;
    set  @bmqtybala =  0;
    set  @byqtysumcredit = 0;
    set  @byqtysumdebit =  0;
    set  @tmqtysumcredit = 0;
    set  @tmqtysumdebit =  0;

    select @nbalyear=nbalyear,@nbalmonth=nbalmonth,@bmbala=bmbala,@bysumcredit=bysumcredit,@bysumdebit=bysumdebit,
      @tmsumcredit=tmsumcredit,@tmsumdebit=tmsumdebit,@tmsum=tmsum,
      @fbmbala=fbmbala,@fbysumcredit=fbysumcredit,@fbysumdebit=fbysumdebit,
      @ftmsumcredit=ftmsumcredit,@ftmsumdebit=ftmsumdebit,@ftmsum=ftmsum,
      @bmqtybala=bmqtybala,@byqtysumdebit=byqtysumdebit,@byqtysumcredit=byqtysumcredit,
      @tmqtysumdebit=tmqtysumdebit,@tmqtysumcredit=tmqtysumcredit
    from dbo.f_msproductgetpriorinfo(@moneyid,@productid,@subjectid,@nyear,@nmonth,@filid)

    set @bmbala = isnull(@bmbala,0)
    set @bysumcredit = isnull(@bysumcredit,0)
    set @bysumdebit = isnull(@bysumdebit,0)
    set @tmsumcredit = isnull(@tmsumcredit,0)
    set @tmsumdebit = isnull(@tmsumdebit,0)

    set @bysumcredit=@tmsumcredit + @bysumcredit
    set @bysumdebit =@tmsumdebit  + @bysumdebit

    set @bmbala=@bmbala+@tmsumdebit-@tmsumcredit

    set @fbmbala = isnull(@fbmbala,0)
    set @fbysumcredit = isnull(@fbysumcredit,0)
    set @fbysumdebit =isnull(@fbysumdebit,0)
    set @ftmsumcredit =isnull(@ftmsumcredit,0)
    set @ftmsumdebit =isnull(@ftmsumdebit,0)

    set @fbysumcredit=@ftmsumcredit + @fbysumcredit
    set @fbysumdebit =@ftmsumdebit  + @fbysumdebit

    set @fbmbala=@fbmbala+@ftmsumdebit-@ftmsumcredit

    set @bmqtybala = isnull(@bmqtybala,0)
    set @byqtysumcredit = isnull(@byqtysumcredit,0)
    set @byqtysumdebit =isnull(@byqtysumdebit,0)
    set @tmqtysumcredit =isnull(@tmqtysumcredit,0)
    set @tmqtysumdebit =isnull(@tmqtysumdebit,0)

    set @byqtysumcredit=@tmqtysumcredit + @byqtysumcredit
    set @byqtysumdebit =@tmqtysumdebit  + @byqtysumdebit

    set @bmqtybala=@bmqtybala+@tmqtysumdebit-@tmqtysumcredit

    if (@nyear<>@nbalyear)
    begin
      set @bysumcredit=0
      set @bysumdebit=0
      set @fbysumcredit=0
      set @fbysumdebit=0
      set @byqtysumcredit = 0
      set @byqtysumdebit = 0
    end

    
    insert tft_msproduct(
      productid,
      subjectid,
      nyear,
      nmonth,
      filid,
      moneyid,
      bmbala,
      bysumdebit,
      bysumcredit,
      tmsumdebit,
      tmsumcredit,
      tmsum,
      fbmbala,
      fbysumdebit,
      fbysumcredit,
      ftmsumdebit,
      ftmsumcredit,
      ftmsum,
      bmqtybala,
      byqtysumdebit,
      byqtysumcredit,
      tmqtysumdebit,
      tmqtysumcredit,
      odata
    ) select 
      productid,
      subjectid,
      nyear,
      nmonth,
      isnull(@filid,0),
      isnull(@moneyid,0),
      isnull(@bmbala,0),
      isnull(@bysumdebit,0),
      isnull(@bysumcredit,0),
      isnull(tmsumdebit,0),
      isnull(tmsumcredit,0),
      isnull(tmsum,0),
      isnull(@fbmbala,0),
      isnull(@fbysumdebit,0),
      isnull(@fbysumcredit,0),
      isnull(ftmsumdebit,0),
      isnull(ftmsumcredit,0),
      isnull(ftmsum,0),
      isnull(@bmqtybala,0),
      isnull(@byqtysumdebit,0),
      isnull(@byqtysumcredit,0),
      isnull(tmqtysumdebit,0),
      isnull(tmqtysumcredit,0),
      odata
    from inserted where productid=@productid and subjectid=@subjectid and nyear=@nyear and nmonth=@nmonth and filid=@filid and moneyid=@moneyid
    fetch next from local_cursor into @productid,@subjectid,@nyear,@nmonth,@filid,@moneyid
  end
  close local_cursor
  deallocate local_cursor
end

